﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using VSDBM.Code.dto;
using System.Data;
using MySql.Data.MySqlClient;


namespace VSDBM.Code.dao
{
    public class OrganismDAO
    {

        public static String add(Organism organism)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();

                DataSet ds = select(" WHERE name = '" + organism.name + "' AND status = 1", "");
                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    MySqlCommand query = new MySqlCommand("INSERT INTO organisms (name,status) VALUES ('" + organism.name + "'" + ",1)", connection);
                    query.ExecuteNonQuery();
                    return "Added!";
                }

                return "Organism '" + organism.name + "' already exist.";
            }
            catch (Exception e)
            {
                return "ERRO! - " + e.Message;
            }
        }

        public static Boolean delete(Organism organism)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();
                DataSet ds = ReferenceDAO.select("WHERE idorganism=" + organism.id, "");
                Reference refx = new Reference();
                refx.id = Convert.ToInt32(ds.Tables[0].Rows[0]["id"]);
                ReferenceDAO.delete(refx);
                List<Subtype> subtypelist = SubtypeDAO.DStoList(SubtypeDAO.select("WHERE idorganisms =" + organism.id, ""));
                foreach (Subtype subtype in subtypelist)
                {
                    SubtypeDAO.delete(subtype);
                }
                List<Sequence> sequencelist = SequenceDAO.DStoList(SequenceDAO.select("WHERE idorganism=" + organism.id, ""));
                foreach (Sequence sequence in sequencelist)
                {
                    SequenceDAO.delete(sequence);
                }
                MySqlCommand query = new MySqlCommand("DELETE FROM organisms WHERE id = " + organism.id + "", connection);
                query.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public static void setActive(int id)
        {
            MySqlConnection connection = Connection.getConnection();
            MySqlCommand query = new MySqlCommand("UPDATE organisms SET status=1 WHERE id=" + id, connection);
            try
            {
                query.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static DataSet select(String filters, String orderby)
        {

            DataSet dSet = new DataSet();
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = new MySqlCommand("SELECT * FROM organisms " + filters + " " + orderby, connection);
                adapter.Fill(dSet);
                return dSet;
            }
            catch (Exception e)
            {
                return dSet;
            }
        }

        public static Organism Fill(int id, String name, int status)
        {
            Organism organism = new Organism();
            organism.id = id;
            organism.name = name;
            organism.status = status;
            return organism;
        }

        public static List<Organism> DStoList(DataSet ds)
        {
            List<Organism> list = new List<Organism>();
            try
            {
                for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    DataTable registro = ds.Tables[0];
                    list.Add(Fill(int.Parse(registro.Rows[i]["id"].ToString()), registro.Rows[i]["name"].ToString(), int.Parse(registro.Rows[i]["status"].ToString())));
                }

            }
            catch (Exception e) { }
            return list;
        }

        public static int setOrganismGetId(String Organism)
        {
            int idOrganism = 0;
            OrganismDAO.add(new dto.Organism(0, Organism, 1));
            DataSet ds = OrganismDAO.select("WHERE name='" + Organism + "'", "");
            idOrganism = Convert.ToInt32(ds.Tables[0].Rows[0][0]);

            return idOrganism;


        }




    }
}